<?php

class lay extends CI_Model {
    /*
      Determines if a given item_id is an item
     */

    function exists($item_id) {
        $this->db->from('layby');
        $this->db->where('item_id', $item_id);
        $query = $this->db->get();

        return ($query->num_rows() == 1);
    }

    function count_all() {
        $this->db->from('layby');
        $this->db->group_by("sale_id");
        return $this->db->count_all_results();
    }

    /*
      Returns all the layby
     */

    function get_all($limit = 10000, $offset = 0) {
        $this->db->from('layby', 'sales', 'people','customers');
        $this->db->join('sales', 'layby.sale_id=sales.sale_id');
        $this->db->join('customers', 'customers.person_id = sales.customer_id','left');
        $this->db->join('people', 'people.person_id=customers.person_id','left');
        $this->db->where('sales.deleted', 0);
        $this->db->group_by("layby.sale_id");
        $this->db->order_by("layby_id", "desc");
        $this->db->limit($limit);
        $this->db->offset($offset);
        $layby = $this->db->get()->result();
        for ($index = 0; $index < count($layby); $index++) {
            $lay = $layby[$index];
            $lay->total = $this->get_total($lay->sale_id);
            $lay->paid = $this->deposits_total($lay->sale_id);
            $lay->balance = $this->get_balance($lay->sale_id);
            $layby[$index] = $lay;
        }
        return $layby;
    }

    /*
      Inserts or updates a item
     */

    function save(&$item_data, $item_id = false) {
        if (!$item_id or !$this->exists($item_id)) {
            if ($this->db->insert('layby', $item_data)) {

                $item_data['item_id'] = $this->db->insert_id();
                return true;
            }
            return false;
        }

        $this->db->where('item_id', $item_id);
        return $this->db->update('items', $item_data);
    }

    function get_deposit_list($sale_id) {
        $this->db->from('layby');
        $this->db->where('sale_id', $sale_id);
        $this->db->order_by("layby.layby_id", "asc");
        return $this->db->get()->result();
    }

    function get_balance($sale_id) {
        $this->db->select_min('balance');
        $this->db->from('layby');
        $this->db->where('sale_id', $sale_id);
        $this->db->group_by("layby.sale_id");
        return $this->db->get()->row()->balance;
    }
    
    function get_layby_last($sale_id) {
        $this->db->select_max('layby_id');
        $this->db->from('layby');
        $this->db->where('sale_id', $sale_id);
        $this->db->group_by("layby.sale_id");
        return $this->get_layby_by_id($this->db->get()->row()->layby_id);
    }
    
    function get_layby_by_id($layby_id) {
        $this->db->from('layby');
        $this->db->where('layby_id', $layby_id);
        return $this->db->get()->row();
    }

    function add_payment($sale_id, $payment_type, $payment_amount) {
        $item_data = array(
            'sale_id' => $sale_id,
            'payment_type' => $payment_type,
            'payment_amount' => $payment_amount
        );
        if ($this->db->insert('sales_payments', $item_data)) {
            return true;
        }
        return false;
    }

    function get_layby_search_suggestions($search, $limit = 25) {
        $suggestions = array();
        $this->db->from('layby', 'sales');
        $this->db->join('sales', 'layby.sale_id = sales.sale_id');
        $this->db->like('sales.sale_id', $search);
        $this->db->where('sales.deleted', 0);
        $this->db->group_by("layby.sale_id");
        $by_sale = $this->db->get();
        foreach ($by_sale->result() as $row) {
            $suggestions[] = array('value' => $row->sale_id, 'label' => $row->sale_id);
        }

        $this->db->from('people', 'sales', 'layby');
        $this->db->join('sales', 'sales.customer_id = people.person_id');
        $this->db->join('layby', 'layby.sale_id = sales.sale_id');
        $this->db->like('people.phone_number', $search);
        $this->db->group_by("layby.sale_id");
        $this->db->where('sales.deleted', 0);
        $by_phone = $this->db->get();
        foreach ($by_phone->result() as $row) {
            $suggestions[] = array('value' => $row->sale_id, 'label' => $row->phone_number);
        }

        $this->db->from('items', 'sales', 'sales_items', 'layby');
        $this->db->join('sales_items', 'sales_items.item_id = items.item_id', 'left');
        $this->db->join('sales', 'sales_items.sale_id = sales.sale_id');
        $this->db->join('layby', 'layby.sale_id = sales.sale_id');
        $this->db->like('items.item_number', $search);
        $this->db->where('sales.deleted', 0);
        $this->db->group_by("layby.sale_id");
        $by_phone = $this->db->get();
        foreach ($by_phone->result() as $row) {
            $suggestions[] = array('value' => $row->sale_id, 'label' => $row->item_number);
        }

        $this->db->from('items', 'sales', 'sales_items', 'layby');
        $this->db->join('sales_items', 'sales_items.item_id = items.item_id');
        $this->db->join('sales', 'sales_items.sale_id = sales.sale_id');
        $this->db->join('layby', 'layby.sale_id = sales.sale_id');
        $this->db->like('items.name', $search);
        $this->db->where('sales.deleted', 0);
        $this->db->group_by("layby.sale_id");
        $by_phone = $this->db->get();
        foreach ($by_phone->result() as $row) {
            $suggestions[] = array('value' => $row->sale_id, 'label' => $row->name);
        }

        //only return $limit suggestions
        if (count($suggestions > $limit)) {
            $suggestions = array_slice($suggestions, 0, $limit);
        }
        return $suggestions;
    }

    function get_layby_search_list_suggestions($search, $limit = 25) {
        $suggestions = array();
        $this->db->from('layby', 'sales');
        $this->db->join('sales', 'layby.sale_id = sales.sale_id');
        $this->db->like('sales.sale_id', $search);
        $this->db->where('sales.deleted', 0);
        $this->db->group_by("layby.sale_id");
        $by_sale = $this->db->get();
        foreach ($by_sale->result() as $row) {
            $suggestions[] = array('value' => $row->sale_id, 'label' => $row->sale_id);
        }

        $this->db->from('people', 'sales', 'layby');
        $this->db->join('sales', 'sales.customer_id = people.person_id');
        $this->db->join('layby', 'layby.sale_id = sales.sale_id');
        $this->db->like('people.phone_number', $search);
        $this->db->group_by("layby.sale_id");
        $this->db->where('sales.deleted', 0);
        $by_phone = $this->db->get();
        foreach ($by_phone->result() as $row) {
            $suggestions[] = array('label' => $row->phone_number);
        }

        $this->db->from('items', 'sales', 'sales_items', 'layby');
        $this->db->join('sales_items', 'sales_items.item_id = items.item_id', 'left');
        $this->db->join('sales', 'sales_items.sale_id = sales.sale_id');
        $this->db->join('layby', 'layby.sale_id = sales.sale_id');
        $this->db->like('items.item_number', $search);
        $this->db->where('sales.deleted', 0);
        $this->db->group_by("layby.sale_id");
        $by_phone = $this->db->get();
        foreach ($by_phone->result() as $row) {
            $suggestions[] = array('label' => $row->item_number);
        }

        $this->db->from('items', 'sales', 'sales_items', 'layby');
        $this->db->join('sales_items', 'sales_items.item_id = items.item_id');
        $this->db->join('sales', 'sales_items.sale_id = sales.sale_id');
        $this->db->join('layby', 'layby.sale_id = sales.sale_id');
        $this->db->like('items.name', $search);
        $this->db->where('sales.deleted', 0);
        $this->db->group_by("layby.sale_id");
        $by_phone = $this->db->get();
        foreach ($by_phone->result() as $row) {
            $suggestions[] = array('label' => $row->name);
        }

        //only return $limit suggestions
        if (count($suggestions > $limit)) {
            $suggestions = array_slice($suggestions, 0, $limit);
        }
        return $suggestions;
    }

    function get_sale_items($sale_id) {
        $this->db->from('sales_items', 'items');
        $this->db->where('sale_id', $sale_id);
        $this->db->join('items', 'items.item_id = sales_items.item_id');
        return $this->db->get();
    }

    function get_items_in_cart($sale_id) {
        $this->db->from('sales_items', 'items');
        $this->db->where('sale_id', $sale_id);
        $this->db->join('items', 'items.item_id = sales_items.item_id');
        $query = $this->db->get();
        return ($query->num_rows());
    }

    function get_sale_payments($sale_id) {
        $this->db->from('sales_payments');
        $this->db->where('sale_id', $sale_id);
        return $this->db->get();
    }

    //Alain Multiple Payments
    function get_payments_total($sale_id) {
        $subtotal = 0;
        foreach ($this->get_sale_payments($sale_id)->result_array() as $payments) {
            $subtotal+=$payments['payment_amount'];
        }
        return to_currency_no_money($subtotal);
    }

    function get_total($sale_id = false) {
        $total = 0;
        foreach ($this->get_sale_items($sale_id)->result_array() as $item) {
            if ($item['discount_percent'] < 0) {
                $total+=($item['item_unit_price'] * $item['item_unit_price'] - abs($item['discount_percent']));
            } else {
                $total+=($item['item_unit_price'] * $item['quantity_purchased'] + abs($item['discount_percent']));
            }
        }

        foreach ($this->get_taxes($sale_id) as $tax) {
            $total+=$tax;
        }
        return to_currency_no_money($total);
    }

    function get_comment($sale_id) {
        $this->db->from('sales');
        $this->db->where('sale_id', $sale_id);
        return $this->db->get()->row()->comment;
    }

    function get_customer_id($sale_id) {
        $this->db->from('sales');
        $this->db->where('sale_id', $sale_id);
        return $this->db->get()->row()->customer_id;
    }

    function get_subtotal($sale_id = false) {
        $total = 0;
        foreach ($this->get_sale_items($sale_id)->result_array() as $item) {
            if ($item['discount_percent'] < 0) {
                $total+=($item['item_cost_price'] * $item['item_cost_price'] - abs($item['discount_percent']));
            } else {
                $total+=($item['item_cost_price'] * $item['quantity_purchased'] + abs($item['discount_percent']));
            }
        }

        return to_currency_no_money($total);
    }

    //Alain Multiple Payments
    function get_amount_due($sale_id = false) {
        $amount_due = 0;
        $payment_total = $this->get_payments_total($sale_id);
        $sales_total = $this->get_total($sale_id);
        $amount_due = to_currency_no_money($sales_total - $payment_total);
        return $amount_due;
    }

    function deposits_total($sale_id) {
        $total = 0;
        foreach ($this->get_deposits($sale_id)->result_array() as $deposit) {
            $total+=$deposit['deposit'];
        }
        return to_currency_no_money($total);
    }

    function get_deposits($sale_id = false) {
        $this->db->from('layby');
        $this->db->where('sale_id', $sale_id);
        return $this->db->get();
    }

    function get_taxes($sale_id = false) {
        $taxes = array();
        // Get main CI object handle and load model
        $CI = & get_instance();
        $CI->load->model('sale');
        $CI->load->model('Item_taxes');
        $CI->load->model('Item_kit_taxes');
        if ($sale_id) {

            $taxes_from_sale = array_merge($this->Sale->get_sale_items_taxes($sale_id), $this->Sale->get_sale_item_kits_taxes($sale_id));
            foreach ($taxes_from_sale as $key => $tax_item) {
                $name = $tax_item['percent'] . '% ' . $tax_item['name'];

                if ($tax_item['cumulative']) {
                    $prev_tax = $taxes[$taxes_from_sale[$key - 1]['percent'] . '% ' . $taxes_from_sale[$key - 1]['name']];
                    $tax_amount = (($tax_item['price'] * $tax_item['quantity'] - $tax_item['price'] * $tax_item['quantity'] * $tax_item['discount'] / 100) + $prev_tax) * (($tax_item['percent']) / 100);
                } else {
                    $tax_amount = ($tax_item['price'] * $tax_item['quantity'] - $tax_item['price'] * $tax_item['quantity'] * $tax_item['discount'] / 100) * (($tax_item['percent']) / 100);
                }

                if (!isset($taxes[$name])) {
                    $taxes[$name] = 0;
                }
                $taxes[$name] += $tax_amount;
            }
        } else {

            $customer = $this->Sale->get_customer($sale_id);

            //Do not charge sales tax if we have a customer that is not taxable
            if (!$customer->taxable and $customer_id != -1) {
                return array();
            }
            foreach ($this->get_sale_items()->result_array() as $line => $item) {
                $tax_info = isset($item['item_id']) ? $this->Item_taxes->get_info($item['item_id']) : $this->Item_kit_taxes->get_info($item['item_kit_id']);
                foreach ($tax_info as $key => $tax) {
                    $name = $tax['percent'] . '% ' . $tax['name'];

                    if ($tax['cumulative']) {
                        $prev_tax = $taxes[$tax_info[$key - 1]['percent'] . '% ' . $tax_info[$key - 1]['name']];
                        $tax_amount = (($item['item_cost_price'] * $item['quantity_purchased'] - $item['item_cost_price'] * $item['quantity_purchased'] * $item['discount_percent'] / 100) + $prev_tax) * (($tax['percent']) / 100);
                    } else {
                        $tax_amount = ($item['item_cost_price'] * $item['quantity_purchased'] - $item['item_cost_price'] * $item['quantity_purchased'] * $item['discount_percent'] / 100) * (($tax['percent']) / 100);
                    }

                    if (!isset($taxes[$name])) {
                        $taxes[$name] = 0;
                    }
                    $taxes[$name] += $tax_amount;
                }
            }
        }
        return $taxes;
    }

    /*
      Preform a search on layby
     */

    function search($search, $limit = 20) {
        $this->db->from('layby', 'sales', 'people', 'items', 'sales_items');
        $this->db->join('sales', 'layby.sale_id=sales.sale_id');
        $this->db->join('people', 'sales.customer_id=people.person_id');
        $this->db->join('sales_items', 'sales_items.sale_id = sales.sale_id');
        $this->db->join('items', 'sales_items.item_id = items.item_id');
        $this->db->where("(phppos_sales.sale_id LIKE '%" . $this->db->escape_like_str($search) . "%' or 
		phppos_people.phone_number LIKE '%" . $this->db->escape_like_str($search) . "%' or 
		phppos_items.item_number LIKE '%" . $this->db->escape_like_str($search) . "%' or 
		phppos_items.name LIKE '%" . $this->db->escape_like_str($search) . "%') and phppos_sales.deleted=0");
        $this->db->group_by("layby.sale_id");
        $this->db->order_by("people.last_name", "asc");
        $this->db->limit($limit);
        $results = $this->db->get()->result();
        for ($index = 0; $index < count($results); $index++) {
            $result = $results[$index];
            $result->total = $this->get_total($result->sale_id);
            $result->paid = $this->deposits_total($result->sale_id);
            $results[$index] = $result;
        }
        return $results;
    }

}

?>
